/**
 * The database package of the application.
 */
package fr.umlv.m2.android.quizz.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteDatabase.CursorFactory;
import android.database.sqlite.SQLiteOpenHelper;

/**
 * This class defines the database open helper for the application.
 * 
 * @author Gambouele Bertrand.
 * @author Viravout Alexandre.
 */
public class QuizzDatabaseOpenHelper extends SQLiteOpenHelper {
	/**
	 * The user id column name.
	 */
	private static final String ID = "id";
	/**
	 * The user name column name.
	 */
	private static final String NAME = "name";
	/**
	 * The user id column name.
	 */
	private static final String USER_ID = "userId";
	/**
	 * The level id column name.
	 */
	private static final String LEVEL_ID = "levelId";
	/**
	 * The theme id column name.
	 */
	private static final String THEME_ID = "themeId";
	/**
	 * The question id column name.
	 */
	private static final String QUESTION_ID = "questionId";
	/**
	 * The answer id column name.
	 */
	private static final String ANSWER_ID = "answerId";
	
	/**
	 * The user table name.
	 */
	private static final String USER_TABLE = "user";
	
	/**
	 * The theme table name.
	 */
	private static final String THEME_TABLE = "theme";
	
	/**
	 * The level table name.
	 */
	private static final String LEVEL_TABLE = "level";
	/**
	 * The level number of question column name.
	 */
	private static final String LEVEL_NB_QUESTION = "nbQuestion";
	
	/**
	 * The score table name.
	 */
	private static final String SCORE_TABLE = "score";
	/**
	 * The score tournament rank column name.
	 */
	private static final String SCORE_TOURNAMENT_RANK = "tournamentRank";
	/**
	 * The score number of good answer column name.
	 */
	private static final String SCORE_GOOD_ANSWER = "goodAnswer";
	/**
	 * The score number of bad answer column name.
	 */
	private static final String SCORE_BAD_ANSWER = "badAnswer";
	/**
	 * The score percentage of success column name.
	 */
	private static final String SCORE_SUCCESS_PERCENTAGE = "successPercentage";
	/**
	 * The score time column name.
	 */
	private static final String SCORE_TIME = "time";
	
	/**
	 * The question table name.
	 */
	private static final String QUESTION_TABLE = "question";
	/**
	 * The question text column name.
	 */
	private static final String QUESTION_TEXT = "questionText";
	
	/**
	 * The answer table name.
	 */
	private static final String ANSWER_TABLE = "answer";
	/**
	 * The answer text column name.
	 */
	private static final String ANSWER_TEXT = "answerText";
	
	/**
	 * The user table create query.
	 */
	private static final String CREATE_USER_TABLE = "CREATE TABLE " + USER_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		NAME + " TEXT NOT NULL" +
		");";
	
	/**
	 * The theme table create query.
	 */
	private static final String CREATE_THEME_TABLE = "CREATE TABLE " + THEME_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		NAME + " TEXT NOT NULL, " +
		LEVEL_ID + " INTEGER NOT NULL" +
		");";
	
	/**
	 * The level table create query.
	 */
	private static final String CREATE_LEVEL_TABLE = "CREATE TABLE " + LEVEL_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		NAME + " TEXT NOT NULL, " +
		LEVEL_NB_QUESTION + " INTEGER NOT NULL" +
		");";
	
	/**
	 * The score table create query.
	 */
	private static final String CREATE_SCORE_TABLE = "CREATE TABLE " + SCORE_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		SCORE_TOURNAMENT_RANK + " INTEGER DEFAULT NULL, " +
		SCORE_GOOD_ANSWER + " INTEGER NOT NULL, " +
		SCORE_BAD_ANSWER + " INTEGER NOT NULL, " +
		SCORE_SUCCESS_PERCENTAGE + " INTEGER NOT NULL, " +
		SCORE_TIME + " INTEGER DEFAULT NULL, " +
		USER_ID + " INTEGER NOT NULL, " +
		LEVEL_ID + " INTEGER NOT NULL, " +
		THEME_ID + " INTEGER NOT NULL" +
		");";
	
	/**
	 * The question table create query.
	 */
	private static final String CREATE_QUESTION_TABLE = "CREATE TABLE " + QUESTION_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		QUESTION_TEXT + " TEXT NOT NULL, " +
		ANSWER_ID + " INTEGER NOT NULL, " +
		LEVEL_ID + " INTEGER NOT NULL, " +
		THEME_ID + " INTEGER NOT NULL" +
		");";
	
	/**
	 * The answer table create query.
	 */
	private static final String CREATE_ANSWER_TABLE = "CREATE TABLE " + ANSWER_TABLE + " (" +
		ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " +
		ANSWER_TEXT + " TEXT NOT NULL, " +
		QUESTION_ID + " INTEGER NOT NULL, " +
		LEVEL_ID + " INTEGER NOT NULL, " +
		THEME_ID + " INTEGER NOT NULL" +
		");";
	
	/**
	 * The user table drop query.
	 */
	private static final String DROP_USER_TABLE = "DROP TABLE IF EXISTS " + USER_TABLE + ";";
	/**
	 * The theme table drop query.
	 */
	private static final String DROP_THEME_TABLE = "DROP TABLE IF EXISTS " + THEME_TABLE + ";";
	/**
	 * The level table drop query.
	 */
	private static final String DROP_LEVEL_TABLE = "DROP TABLE IF EXISTS " + LEVEL_TABLE + ";";
	/**
	 * The score table drop query.
	 */
	private static final String DROP_SCORE_TABLE = "DROP TABLE IF EXISTS " + SCORE_TABLE + ";";
	/**
	 * The question table drop query.
	 */
	private static final String DROP_QUESTION_TABLE = "DROP TABLE IF EXISTS " + QUESTION_TABLE + ";";
	/**
	 * The answer table drop query.
	 */
	private static final String DROP_ANSWER_TABLE = "DROP TABLE IF EXISTS " + ANSWER_TABLE + ";";
 
	/**
	 * Create the database open helper object.
	 * 
	 * @param context the context.
	 * @param name the database name.
	 * @param factory the factory.
	 * @param version the database version.
	 */
	public QuizzDatabaseOpenHelper(Context context, String name, CursorFactory factory, int version) {
		super(context, name, factory, version);
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_THEME_TABLE);
		db.execSQL(CREATE_LEVEL_TABLE);
		db.execSQL(CREATE_SCORE_TABLE);
		db.execSQL(CREATE_QUESTION_TABLE);
		db.execSQL(CREATE_ANSWER_TABLE);
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL(DROP_USER_TABLE);
		db.execSQL(DROP_THEME_TABLE);
		db.execSQL(DROP_LEVEL_TABLE);
		db.execSQL(DROP_SCORE_TABLE);
		db.execSQL(DROP_QUESTION_TABLE);
		db.execSQL(DROP_ANSWER_TABLE);
		this.onCreate(db);
	}
	
	/**
	 * Clear the specific table.
	 * 
	 * @param table the specific table.
	 */
	public void clearTable(SQLiteDatabase db, String table) {
		if(table.equals(USER_TABLE)) {
			db.execSQL(DROP_USER_TABLE);
			db.execSQL(CREATE_USER_TABLE);
		} else if(table.equals(LEVEL_TABLE)) {
			db.execSQL(DROP_LEVEL_TABLE);
			db.execSQL(CREATE_LEVEL_TABLE);
		} else if(table.equals(THEME_TABLE)) {
			db.execSQL(DROP_THEME_TABLE);
			db.execSQL(CREATE_THEME_TABLE);
		} else if(table.equals(SCORE_TABLE)) {
			db.execSQL(DROP_SCORE_TABLE);
			db.execSQL(CREATE_SCORE_TABLE);
		} else if(table.equals(QUESTION_TABLE)) {
			db.execSQL(DROP_QUESTION_TABLE);
			db.execSQL(CREATE_QUESTION_TABLE);
		} else if(table.equals(ANSWER_TABLE)) {
			db.execSQL(DROP_ANSWER_TABLE);
			db.execSQL(CREATE_ANSWER_TABLE);
		}
	}
}
